% Generates the column sums of the spatial weights matrix excluding the VTNIC

% MATLAB PART: 
% File Inputs: 
%   - SCnetwork2.csv
%   - compustat_cleaned.csv

% File Outputs:
%   - SC_column_sums_No_VTNIC.csv

% Stata PART:
% File Inputs:
%   - SC_column_sums_No_VTNIC.csv
% File Outputs:
%   - SC_column_sums_No_VTNIC.dta ;

% ---------------- PATHS  ---------------- %
clear all

base = '/Users/path';
BaseDir = fullfile('path');
global data_dir;
data_dir = 'your_path';
global table_dir;
table_dir = 'your_path';

addpath(genpath(fullfile(BaseDir,'SpatialModels')));

% ---------------- LOAD ALL DATASETS ---------------- %

%%% --- ANNUAL Data --- %%%
% Compustat, Annual
datafname    = fullfile(base, d_dir, 'compustat_cleaned_allVars2.csv');
% Network, Annual
SCname       = fullfile(base, d_dir, 'SCnetwork2.csv');


% ---------------- LOAD ALL DATASETS ---------------- %
% ----- COMPUSTAT datasets -----
% Annual 
SrcData  = readtable(datafname);
SrcData2 = readtable(datafname2);

% Network - Annual
SCW = readtable(SCname);
SCW = SCW(SCW{:,'source'} ~= 3,:) ;


workspace;

% ---- DEFINE TABLES

Table2_temp = fullfile(base, t_dir, 'Table2_2024_10_02.xlsx');

% Define variables outside the loop
VarNames.idpair = {'gvkey1','gvkey2'};
VarNames.wscore = 'score';
VarNames.idvar  = 'gvkey';
VarNames.tvar   = 'fyear';

yvar  = 'capx_at';
% Define models
models(1).xvars =  {'l_logSale', 'l_cash', 'l_zscore', 'l_roa', 'l_mb', 'l_bl'};

% ----- TABLE 2 and 3 
for spec = 1:1
    VarNames.yvar  = yvar;
    VarNames.xvars = models(spec).xvars;
    sheet = spec;

    tmp = [SrcData{:,yvar} SrcData{:,models(spec).xvars}];
    SrcData_temp = SrcData(sum(isnan(tmp),2)==0,:);
    myData = SrcData_temp(SrcData_temp{:,'fyear'} >= 2003,:);

        sarres = EstimateSAR(myData, SCW, VarNames);
        SpatialPrint(sarres, Table2_temp, sheet);
end

% ------ CALCULATE COLUMN SUMS EXCLUDING THE VTNIC -----%


Data=myData; 
AllW = SCW;

% merge (join) tables to ensure W matches data
idpair = VarNames.idpair;
wscore = VarNames.wscore;
idvar  = VarNames.idvar ;
tvar   = VarNames.tvar  ;
xvars  = VarNames.xvars ;
yvar   = VarNames.yvar  ;


idpair = VarNames.idpair;
ids = unique(AllW(:,[idpair(1),tvar]), 'rows');
ids.Properties.VariableNames{idpair(1)} = idvar;
ids = sortrows(ids,{tvar,idvar});

ModData = sortrows(innerjoin(Data, ids),{tvar,idvar});

% eliminate missing values
TF = (sum(isnan(ModData{:,[yvar, xvars]}), 2) == 0);
ModData    = ModData(TF,:);

% Initialize cell arrays to hold the matrices
begYear = min(ModData{:,tvar});
endYear = max(ModData{:,tvar});

W = cell(1,endYear - begYear + 1);
IDS = cell(1,endYear - begYear + 1);
X = cell(1,endYear - begYear + 1);
Y = cell(1,endYear - begYear + 1);

ID = ModData{:,{idvar,tvar}};

Z_concat  = cell2table(cell(0,4), 'VariableNames', {'gvkey', 'index', 'columnsum', 'fyear'});

for i = 1:(endYear - begYear + 1);

    year = begYear + i - 1;

    % Z =[table2array(sarres.IDS{i}) (eye(3904) - 0.5*sarres.W{3})'\ones(3904,1) ] ; 
    Z =[table2array(sarres.IDS{i}) (eye(size(sarres.W{i},1)) - (sarres.rho_out(1,3))*sarres.W{i})'\ones(size(sarres.W{i},1),1) ] ; 
    Z = array2table(Z);
    Z.fyear(:,1) = year;
    Z.Properties.VariableNames = ["gvkey","index", "columnsum", "fyear"];    
    Z_concat = [Z_concat ; Z] ;
end;

mean(Z_concat.columnsum)

writetable(Z_concat, fullfile(base, '/Dropbox/My Projects/0) Financial Constraints_/Data and Code/SC_column_sums_No_VTNIC.csv') ) ;


% NOTE: To produce the final file, import the dataset into Stata and save it. 
% import delimited "SC_column_sums_No_VTNIC.csv", clear
%     rename columnsum columnsum_noVTNIC
%     winsor2 columnsum_noVTNIC
%     drop index
% save "SC_column_sums_No_VTNIC", replace







